package cn.waka.sys.admin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cn.waka.sys.admin.object.Banner;
import cn.waka.sys.admin.object.Column;

@Component
public class BannerDao extends BaseDao {

    public int save(final Banner banner) {
        KeyHolder holder = new GeneratedKeyHolder();
        this.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                String sql = "INSERT INTO s_banner(title,img,resource_id,`status`,is_del,sort,c_time)VALUES(?,?,?,1,0,?,?)";
                PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, banner.getTitle());
                ps.setString(2, banner.getImg());
                ps.setInt(3, banner.getResource_id());
                ps.setInt(4, maxBannerSort() + 1);
                ps.setTimestamp(5, banner.getC_time());
                return ps;
            }
        }, holder);
        int id = holder.getKey().intValue();
        return id;
    }

    public int update(Banner banner) {
        int rs = this.update("UPDATE s_banner SET title=?,img=?,resource_id=? WHERE id=?",
                banner.getTitle(),
                banner.getImg(),
                banner.getResource_id(),
                banner.getId());
        return rs;
    }

    //banner列表
    public List<Map<String, Object>> getBannerList() {
        String sql = "SELECT id,title,img,resource_id,status,c_time FROM s_banner WHERE is_del=0 ORDER BY sort DESC";

        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                row.put("title", rs.getString(2));
                row.put("img", rs.getString(3));
                row.put("resource_id", rs.getInt(4));
                row.put("status", rs.getInt(5));
                row.put("c_time", rs.getTimestamp(6));
                return row;
            }
        });

        return list;
    }

    //查找一个banner
    public Banner getBannerById(int id) {
        String sql = "SELECT b.`id`,b.`title`,b.`img`,b.`resource_id`, b.`status`,b.`c_time`,b.`sort`,r.`title` FROM s_banner b LEFT JOIN s_resource r ON b.`resource_id` = r.`id` WHERE b.`id`=?";

        List<Banner> list = this.query(sql, new RowMapper<Banner>() {
            @Override
            public Banner mapRow(ResultSet rs, int rowNum) throws SQLException {
                Banner banner = new Banner();
                banner.setId(rs.getInt(1));
                banner.setTitle(rs.getString(2));
                banner.setImg(rs.getString(3));
                banner.setResource_id(rs.getInt(4));
                banner.setStatus(rs.getInt(5));
                banner.setC_time(rs.getTimestamp(6));
                banner.setSort(rs.getInt(7));
                banner.setResource_name(rs.getString(8));
                ;
                return banner;
            }

        }, id);

        return list.size() > 0 ? list.get(0) : null;
    }


    //显示|隐藏
    public int isShow(int id, int status) {
        String usql = "update s_banner set status=? where id=?";
        int rs = this.update(usql, status, id);
        return rs;
    }

    //置顶banner
    public int top_banner(int id, int maxSort) {
        int newSort = maxSort + 1;
        String sql = "UPDATE s_banner SET sort=? WHERE id=?";
        return this.update(sql, newSort, id);
    }

    public int top_cut(int sort) {
        String sql = "UPDATE s_banner SET sort=sort-1 WHERE sort>?";
        return this.update(sql, sort);
    }

    public int maxBannerSort() {
        String sql = "SELECT MAX(sort) FROM (SELECT id,sort FROM s_banner)AS X";
        List<Integer> list = this.query(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                int value = rs.getInt(1);
                return value;
            }
        });

        return list.get(0);
    }

    //上移
    public int up_banner(int id, int sort) {
        String sql = "UPDATE s_banner SET sort=? WHERE id=?";
        return this.update(sql, sort + 1, id);
    }

    public int up_cut(int sort) {
        String sql = "UPDATE s_banner SET sort=? WHERE sort=?";
        return this.update(sql, sort, sort + 1);
    }

    //下移
    public int down_banner(int id, int sort) {
        String sql = "UPDATE s_banner SET sort=? WHERE id=?";
        return this.update(sql, sort - 1, id);
    }

    public int down_cut(int sort) {
        String sql = "UPDATE s_banner SET sort=? WHERE sort=?";
        return this.update(sql, sort, sort - 1);
    }

    //广告推荐列表
    public List<Map<String, Object>> getColumnList() {
        String sql = "SELECT id,type,status,sort FROM s_column ORDER BY sort DESC";

        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                int type = rs.getInt(2);
                row.put("id", rs.getInt(1));
                row.put("type", type);
                row.put("status", rs.getInt(3));
                row.put("sort", rs.getInt(4));
                if (type == 1) {
                    int num = countBanner();
                    row.put("num", num);
                } else if (type == 2) {
                    int num = countKinds();
                    row.put("num", num);
                } else {
                    int num = countSelected();
                    row.put("num", num);
                }
                return row;
            }
        });

        return list;
    }

    public int countBanner() {
        String sql = "SELECT id as num FROM s_banner WHERE `status`=1";
        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                return row;
            }
        });
        return list.size();
    }

    //count影片分类
    public int countKinds() {
        String sql = "SELECT id FROM w_dic WHERE `key` LIKE '1,173,%' AND `status` = 1";
        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                return row;
            }
        });
        return list.size();
    }

    //count精选影片
    public int countSelected() {
        String sql = "SELECT id FROM s_resource WHERE is_selected = 1 ";
        List<Map<String, Object>> list = this.query(sql, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> row = new HashMap<String, Object>();
                row.put("id", rs.getInt(1));
                return row;
            }
        });
        return list.size();
    }

    //查找一个column
    public Column getColumnById(int id) {
        String sql = "SELECT id,`type`,`status`,sort FROM s_column WHERE id =?";

        List<Column> list = this.query(sql, new RowMapper<Column>() {
            @Override
            public Column mapRow(ResultSet rs, int rowNum) throws SQLException {
                Column column = new Column();
                column.setId(rs.getInt(1));
                column.setType(rs.getInt(2));
                column.setStatus(rs.getInt(3));
                column.setSort(rs.getInt(4));
                return column;
            }

        }, id);

        return list.size() > 0 ? list.get(0) : null;
    }

    //显示|隐藏
    public int showOrHidden(int id, int status) {
        String sql = "update s_column set status=? where id=?";
        int rs = this.update(sql, status, id);
        return rs;
    }

    //置顶column
    public int top_column(int id, int maxSort) {
        int newSort = maxSort + 1;
        String sql = "UPDATE s_column SET sort=? WHERE id=?";
        return this.update(sql, newSort, id);
    }

    public int top_del(int sort) {
        String sql = "UPDATE s_column SET sort=sort-1 WHERE sort>?";
        return this.update(sql, sort);
    }

    public int maxSort() {
        String sql = "SELECT MAX(sort) FROM (SELECT id,sort FROM s_column)AS X";
        List<Integer> list = this.query(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
                int value = rs.getInt(1);
                return value;
            }
        });

        return list.get(0);
    }

    //上移
    public int up_column(int id, int sort) {
        String sql = "UPDATE s_column SET sort=? WHERE id=?";
        return this.update(sql, sort + 1, id);
    }

    public int up_del(int sort) {
        String sql = "UPDATE s_column SET sort=? WHERE sort=?";
        return this.update(sql, sort, sort + 1);
    }

    //下移
    public int down_column(int id, int sort) {
        String sql = "UPDATE s_column SET sort=? WHERE id=?";
        return this.update(sql, sort - 1, id);
    }

    public int down_del(int sort) {
        String sql = "UPDATE s_column SET sort=? WHERE sort=?";
        return this.update(sql, sort, sort - 1);
    }
}
